# 报表相关序列化器
import logging
from .utils import *
from rest_framework import serializers
from .models import *
from django.utils import timezone
from .shipping import *
from datetime import datetime, timedelta
from django.core.paginator import Paginator
from openpyxl.utils import get_column_letter, column_index_from_string
from .colors import COLORS, ROW_RANGES, ORDER_TYPE_COLORS
from django.db.models import Count


logger = logging.getLogger(__name__)

class ReportSerializer(serializers.Serializer):
    def _process_date_range(self, start_date, end_date):
        """处理日期范围"""
        if not start_date or not end_date:
            today = datetime.now()
            start_date = today.strftime('%Y-%m-%d')
            end_date = today.strftime('%Y-%m-%d')
        else:
            start_date = datetime.strptime(start_date, '%Y-%m-%d')
            end_date = datetime.strptime(end_date, '%Y-%m-%d')
            end_date = end_date + timedelta(days=1)
        
        # 转换为带时区的datetime
        start_date = timezone.make_aware(start_date)
        end_date = timezone.make_aware(end_date)
        
        return start_date, end_date
    
    def get_report_data(self, start_date, end_date):
        """获取报表数据，参考老版本的实现"""
        original_start = start_date
        original_end = end_date
        start_date, end_date = self._process_date_range(start_date, end_date)
        
        # 获取各类型订单数据
        local_orders = LocalOrder.objects.filter(create_time__range=(start_date, end_date))
        transfer_orders = TransferOrder.objects.filter(create_time__range=(start_date, end_date))
        aftersale_orders = AfterSaleOrder.objects.filter(create_time__range=(start_date, end_date))
        relocation_orders = RelocationOrder.objects.filter(create_time__range=(start_date, end_date))
        cross_orders = CrossOrder.objects.filter(create_time__range=(start_date, end_date))
        return_orders = ReturnOrder.objects.filter(create_time__range=(start_date, end_date))
        inventory_receive_orders = InventoryReceiveOrder.objects.filter(create_time__range=(start_date, end_date))
        repair_orders = RepairOrder.objects.filter(create_time__range=(start_date, end_date))
        order_qa_orders = OrderQA.objects.filter(create_time__range=(start_date, end_date))

        # 使用辅助函数处理数据
        local_orders_data = process_local_orders(local_orders)
        transfer_orders_data = process_transfer_orders(transfer_orders)
        aftersale_orders_data = process_aftersale_orders(aftersale_orders)
        relocation_orders_data = process_relocation_orders(relocation_orders)
        cross_orders_data = process_cross_orders(cross_orders)
        return_orders_data = process_return_orders(return_orders)
        inventory_receive_orders_data = process_inventory_receive_orders(inventory_receive_orders)
        repair_orders_data = process_repair_orders(repair_orders)
        order_qa_data = self._process_order_qa_orders(order_qa_orders)

        return {
            'start_date': original_start if original_start else start_date.strftime('%Y-%m-%d'),
            'end_date': original_end if original_end else (end_date - timedelta(days=1)).strftime('%Y-%m-%d'),
            'local_orders': local_orders_data,
            'transfer_orders': transfer_orders_data,
            'aftersale_orders': aftersale_orders_data,
            'relocation_orders': relocation_orders_data,
            'cross_orders': cross_orders_data,
            'return_orders': return_orders_data,
            'inventory_receive_orders': inventory_receive_orders_data,
            'repair_orders': repair_orders_data,
            'order_qa_orders': order_qa_data,
        }
    
    def get_daily_report(self, start_date, end_date):
        start_date, end_date = self._process_date_range(start_date, end_date)
        daily_reports = []
        
        current_date = start_date
        while current_date < end_date:
            local_orders = LocalOrder.objects.filter(create_time__gte=current_date, create_time__lt=current_date + timedelta(days=1))
            
            local_order_count = local_order_is_changecode_count = local_order_is_pickup_count = 0
            local_order_box_count = 0
            
            for order in local_orders:
                local_order_count += 1
                local_order_box_count += safe_int(order.box_count)
                
                # 改码单统计（包括改码+自提的订单，改码+自提只算改码单）
                if order.is_changecode:
                    local_order_is_changecode_count += 1
                
                # 自提单统计（只统计纯自提单，不包括改码+自提）
                if order.is_pickup and not order.is_changecode:
                    local_order_is_pickup_count += 1
            
            regular_order_count = sum(1 for order in local_orders if not order.is_changecode and not order.is_pickup)
            
            transfer_orders = TransferOrder.objects.filter(create_time__gte=current_date, create_time__lt=current_date + timedelta(days=1))
            transfer_order_count = transfer_order_is_pickup_count = transfer_order_box_count = 0
            for order in transfer_orders:
                transfer_order_count += 1
                transfer_order_box_count += safe_int(order.box_count)
                if order.is_pickup: transfer_order_is_pickup_count += 1
            
            regular_transfer_count = transfer_order_count - transfer_order_is_pickup_count
            
            relocation_orders = RelocationOrder.objects.filter(create_time__gte=current_date, create_time__lt=current_date + timedelta(days=1))
            relocation_order_count = sum(safe_int(order.order_count) for order in relocation_orders)
            relocation_order_box_count = sum(safe_int(order.box_count) for order in relocation_orders)
            
            cross_orders = CrossOrder.objects.filter(create_time__gte=current_date, create_time__lt=current_date + timedelta(days=1))
            cross_order_count = sum(safe_int(order.order_count) for order in cross_orders)
            cross_order_box_count = sum(safe_int(order.box_count) for order in cross_orders)
            
            return_orders = ReturnOrder.objects.filter(create_time__gte=current_date, create_time__lt=current_date + timedelta(days=1))
            return_order_count = sum(safe_int(order.order_count) for order in return_orders)
            return_order_box_count = sum(safe_int(order.box_count) for order in return_orders)
            
            inventory_receive_orders = InventoryReceiveOrder.objects.filter(create_time__gte=current_date, create_time__lt=current_date + timedelta(days=1))
            inventory_receive_order_count = sum(safe_int(order.order_count) for order in inventory_receive_orders)
            inventory_receive_order_box_count = sum(safe_int(order.box_count) for order in inventory_receive_orders)
            
            repair_orders = RepairOrder.objects.filter(create_time__gte=current_date, create_time__lt=current_date + timedelta(days=1))
            repair_order_count = sum(safe_int(order.order_count) for order in repair_orders)
            repair_order_box_count = sum(safe_int(order.box_count) for order in repair_orders)

            daily_data = {
                "date": current_date.strftime('%Y-%m-%d'),
                "local_orders": {"orders_count": local_order_count, "is_changecode_count": local_order_is_changecode_count, "is_pickup_count": local_order_is_pickup_count, "regular_order_count": regular_order_count, "box_count": local_order_box_count},
                "transfer_orders": {"orders_count": transfer_order_count, "is_pickup_count": transfer_order_is_pickup_count, "regular_order_count": regular_transfer_count, "box_count": transfer_order_box_count},
                "relocation_orders": {"orders_count": relocation_order_count, "box_count": relocation_order_box_count},
                "cross_orders": {"orders_count": cross_order_count, "box_count": cross_order_box_count},
                "return_orders": {"orders_count": return_order_count, "box_count": return_order_box_count},
                "inventory_receive_orders": {"orders_count": inventory_receive_order_count, "box_count": inventory_receive_order_box_count},
                "repair_orders": {"orders_count": repair_order_count, "box_count": repair_order_box_count},
            }
            
            daily_reports.append(daily_data)
            current_date += timedelta(days=1)
        
        return daily_reports

    def export_excel_report(self, start_date, end_date):
        today = datetime.now().strftime('%Y-%m-%d')
        start_date, end_date = self._process_date_range(start_date, end_date)
        last_date = end_date - timedelta(days=1)
        font_size = 18
        width = 20
        height = 30
        
        # 创建新的Excel文件
        wb = openpyxl.Workbook()
        sheet = wb.active
        sheet.title = '报表数据'

        # 创建订单问答专用sheet
        qa_sheet = wb.create_sheet(title='订单问答')
        
        # 设置订单问答sheet的标题和表头
        insert_excel_by_cell(qa_sheet, 'A1', '订单问答', merge_range='A1:A9', font_size=font_size, is_bold=True, has_border=True, width=width, height=height)
        insert_excel_by_cell(qa_sheet, 'B1', '时间段', merge_range='B1:C3', font_size=font_size, is_bold=True, has_border=True, width=width, height=height)
        
        # 设置时间段
        time_slots = [
            ('B4', 'C4', '9:30-10:00'),
            ('B5', 'C5', '11:30-12:00'),
            ('B6', 'C6', '13:30-14:00'),
            ('B7', 'C7', '15:30-16:00'),
            ('B8', 'C8', '17:30-18:00'),
            ('B9', 'C9', '数量合计')
        ]
        
        for start_cell, end_cell, text in time_slots:
            insert_excel_by_cell(qa_sheet, start_cell, text, merge_range=f'{start_cell}:{end_cell}', font_size=font_size, is_bold=True, has_border=True, width=width, height=height)

        # 日期 项目 A1-C2 (标题区域)
        insert_excel_by_cell(sheet, 'A1', today, merge_range='A1:B2', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['title'])
        insert_excel_by_cell(sheet, 'C1', '项目', merge_range='C1:C2', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['title'])

        # 发货区域 A3-A15
        self._setup_shipping_area(sheet, font_size, width, height)

        # 收货 A16-A26 (收货区域 - 浅蓝色)
        insert_excel_by_cell(sheet, 'A16', '收货', merge_range='A16:A26', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='B4C6E7')
        # 调仓单 (浅绿色)
        insert_excel_by_cell(sheet, 'B16', '调仓单', merge_range='B16:B17', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='C6EFCE')
        insert_excel_by_cell(sheet, 'C16', '单数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='C6EFCE')
        insert_excel_by_cell(sheet, 'C17', '箱数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='C6EFCE')
        # 退换货 (浅黄色)
        insert_excel_by_cell(sheet, 'B18', '退换货', merge_range='B18:B20', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FFEB9C')
        insert_excel_by_cell(sheet, 'C18', '单数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FFEB9C')
        insert_excel_by_cell(sheet, 'C19', '箱数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FFEB9C')
        insert_excel_by_cell(sheet, 'C20', '数量', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FFEB9C')
        # 库存收货 (浅橙色)
        insert_excel_by_cell(sheet, 'B21', '库存收货', merge_range='B21:B22', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FFC7CE')
        insert_excel_by_cell(sheet, 'C21', '单数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FFC7CE')
        insert_excel_by_cell(sheet, 'C22', '箱数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FFC7CE')
        # 回国维修收货 (浅紫色)
        insert_excel_by_cell(sheet, 'B23', '回国维修收货', merge_range='B23:B24', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='E1D5E7')
        insert_excel_by_cell(sheet, 'C23', '单数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='E1D5E7')
        insert_excel_by_cell(sheet, 'C24', '箱数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='E1D5E7')
        # 收货合计 (深绿色)
        insert_excel_by_cell(sheet, 'B25', '收货合计', merge_range='B25:B26', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='A9D08E')
        insert_excel_by_cell(sheet, 'C25', '单数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='A9D08E')
        insert_excel_by_cell(sheet, 'C26', '箱数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='A9D08E')

        # 收发货合计 A27-A28 (深蓝色)
        insert_excel_by_cell(sheet, 'A27', '收发货合计', merge_range='A27:B28', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='4472C4')
        insert_excel_by_cell(sheet, 'C27', '单数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='4472C4')
        insert_excel_by_cell(sheet, 'C28', '箱数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='4472C4')

        # PickUp(提货当日) A29-A30 (橙色)
        insert_excel_by_cell(sheet, 'A29', 'PickUp(提货当日)', merge_range='A29:B30', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FF8C00')
        insert_excel_by_cell(sheet, 'C29', '单数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FF8C00')
        insert_excel_by_cell(sheet, 'C30', '箱数', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color='FF8C00')

        # 未处理订单区域 (全部使用灰色)
        self._setup_unprocessed_areas(sheet, font_size, width, height)



        
        # 设置订单问答sheet的标题和表头
        insert_excel_by_cell(qa_sheet, 'A1', '订单问答', merge_range='A1:A9', font_size=font_size, is_bold=True, has_border=True, width=width, height=height)
        insert_excel_by_cell(qa_sheet, 'B1', '时间段', merge_range='B1:C3', font_size=font_size, is_bold=True, has_border=True, width=width, height=height)
        
        # 设置时间段
        time_slots = [
            ('B4', 'C4', '9:30-10:00'),
            ('B5', 'C5', '11:30-12:00'),
            ('B6', 'C6', '13:30-14:00'),
            ('B7', 'C7', '15:30-16:00'),
            ('B8', 'C8', '17:30-18:00'),
            ('B9', 'C9', '数量合计')
        ]
        
        for start_cell, end_cell, text in time_slots:
            insert_excel_by_cell(qa_sheet, start_cell, text, merge_range=f'{start_cell}:{end_cell}', font_size=font_size, is_bold=True, has_border=True, width=width, height=height)
        
        days = (last_date - start_date).days + 1
        
        # 为订单问答sheet添加日期列头
        qa_col_index = 0
        for i in range(days):
            date = start_date + timedelta(days=i)
            weekday = date.weekday()
            if weekday >= 5:  # 跳过周末
                continue
                
            col_letter = get_column_letter(4 + qa_col_index)  # 从D列开始
            next_col_letter = get_column_letter(5 + qa_col_index)  # 下一列
            weekdays = ['一', '二', '三', '四', '五']
            weekday_text = f'星期{weekdays[weekday]}'
            
            # 插入星期和日期到订单问答sheet（合并单元格）
            insert_excel_by_cell(qa_sheet, f"{col_letter}1", weekday_text, merge_range=f'{col_letter}1:{next_col_letter}1', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            insert_excel_by_cell(qa_sheet, f"{col_letter}2", date.strftime('%m-%d'), merge_range=f'{col_letter}2:{next_col_letter}2', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            
            # 插入操作人和数量标题（不合并单元格）
            insert_excel_by_cell(qa_sheet, f"{col_letter}3", '操作人', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            insert_excel_by_cell(qa_sheet, f"{next_col_letter}3", '数量', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            
            qa_col_index += 2  # 每次增加2，每个日期占两列
        
        # 计算工作日数量
        work_days = 0
        for i in range(days):
            date = start_date + timedelta(days=i)
            weekday = date.weekday()
            if weekday < 5:  # 工作日（周一到周五）
                work_days += 1
        
        # 插入收发货统计和订单问答部分的日期（从D列开始）
        col_index = 0
        for i in range(days):
            date = start_date + timedelta(days=i)
            weekday = date.weekday()
            if weekday >= 5:  # 跳过周末
                continue
                
            col_letter = get_column_letter(4 + col_index)  # 从D列开始（4）
            weekdays = ['一', '二', '三', '四', '五']
            weekday_text = f'星期{weekdays[weekday]}'
            
            # 插入星期和日期到收发货统计部分（第1-2行）
            insert_excel_by_cell(sheet, f"{col_letter}1", weekday_text, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            insert_excel_by_cell(sheet, f"{col_letter}2", date.strftime('%m-%d'), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            
            
            col_index += 1  # 每个日期占一列
        
        
        # 为订单问答sheet添加日期列头
        qa_col_index = 0
        for i in range(days):
            date = start_date + timedelta(days=i)
            weekday = date.weekday()
            if weekday >= 5:  # 跳过周末
                continue
                
            col_letter = get_column_letter(4 + qa_col_index)  # 从D列开始
            next_col_letter = get_column_letter(5 + qa_col_index)  # 下一列
            weekdays = ['一', '二', '三', '四', '五']
            weekday_text = f'星期{weekdays[weekday]}'
            
            # 插入星期和日期到订单问答sheet（合并单元格）
            insert_excel_by_cell(qa_sheet, f"{col_letter}1", weekday_text, merge_range=f'{col_letter}1:{next_col_letter}1', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            insert_excel_by_cell(qa_sheet, f"{col_letter}2", date.strftime('%m-%d'), merge_range=f'{col_letter}2:{next_col_letter}2', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            
            # 插入操作人和数量标题（不合并单元格）
            insert_excel_by_cell(qa_sheet, f"{col_letter}3", '操作人', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            insert_excel_by_cell(qa_sheet, f"{next_col_letter}3", '数量', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color='93A9D7')
            
            qa_col_index += 2  # 每次增加2，每个日期占两列
        
        # 设置日期列和填充数据
        col_index = 0
        date_col_letters = []
        pickup_data_by_date = {}  # 存储每个日期的自提单数据
        
        for i in range(days):
            date = start_date + timedelta(days=i)
            weekday = date.weekday()
            if weekday >= 5:  # 跳过周末
                continue
                
            col_letter = get_column_letter(4 + col_index)  # 从D列开始
            date_col_letters.append(col_letter)
            weekdays = ['一', '二', '三', '四', '五']
            weekday_text = f'星期{weekdays[weekday]}'
            
            # 获取当天的各种订单数据
            local_orders = LocalOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))
            transfer_orders = TransferOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))
            aftersale_orders = AfterSaleOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))
            relocation_orders = RelocationOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))
            cross_orders = CrossOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))
            return_orders = ReturnOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))
            inventory_receive_orders = InventoryReceiveOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))
            repair_orders = RepairOrder.objects.filter(create_time__gte=date, create_time__lt=date + timedelta(days=1))

            # 获取当天的未处理订单数据
            unprocessed_data = self._get_unprocessed_orders_for_date(date)

            # 获取当天的订单问答数据
            order_qa_data = self._get_order_qa_for_date(date)
            
            # 收集自提单数据 - 这里先不收集，后面统一收集
            # pickup_data = self._collect_pickup_data_for_date(local_orders, transfer_orders)
            # pickup_data_by_date[date] = pickup_data
            
            # 插入收发货统计数据到Excel
            self._insert_shipping_data_to_excel(sheet, col_letter, local_orders, transfer_orders, 
                                             aftersale_orders, relocation_orders, cross_orders, 
                                             return_orders, inventory_receive_orders, repair_orders,
                                             font_size, height)
            
            # 插入未处理订单数据到Excel
            self._insert_unprocessed_data_to_excel(sheet, col_letter, unprocessed_data, font_size, height)

            col_index += 1
        
        # 统一收集所有自提单数据
        pickup_data_by_date = self._collect_all_pickup_data(start_date, end_date)
        
        # 插入PickUp行数据
        self._insert_pickup_data_to_excel(sheet, pickup_data_by_date, date_col_letters, start_date, end_date, font_size, height)
        
        # 插入订单问答数据到专用sheet
        qa_col_index = 0
        for i in range(days):
            date = start_date + timedelta(days=i)
            weekday = date.weekday()
            if weekday >= 5:  # 跳过周末
                continue
            qa_col_letter = get_column_letter(4 + qa_col_index * 2)  # 订单问答sheet每个日期占两列
            order_qa_data = self._get_order_qa_for_date(date)
            self._insert_order_qa_data_to_excel(qa_sheet, qa_col_letter, order_qa_data, font_size, height)
            qa_col_index += 1
        
        # 插入订单问答统计信息到A11行开始
        self._insert_order_qa_statistics_to_excel(qa_sheet, start_date, end_date, font_size, height)
        
        # 计算收发货统计部分的日期列数
        shipping_date_cols = []
        col_index = 0
        for i in range(days):
            date = start_date + timedelta(days=i)
            weekday = date.weekday()
            if weekday >= 5:  # 跳过周末
                continue
            shipping_date_cols.append(get_column_letter(4 + col_index))  # 从D列开始
            col_index += 1
        
        # 添加合计列（在收发货统计日期最后列的后面）
        sum_col_letter = get_column_letter(4 + len(shipping_date_cols))
        insert_excel_by_cell(sheet, f"{sum_col_letter}1", "合计", font_size=font_size, is_bold=True, has_border=True, merge_range=f'{sum_col_letter}1:{sum_col_letter}2', width=15, height=height)
        
        # 添加平均列
        avg_col_letter = get_column_letter(5 + len(shipping_date_cols))
        days_count = len(shipping_date_cols)
        insert_excel_by_cell(sheet, f"{avg_col_letter}1", f"平均/{days_count}天", font_size=font_size, is_bold=True, has_border=True, merge_range=f'{avg_col_letter}1:{avg_col_letter}2', width=15, height=height)
        
        # 计算各行合计
        def sum_cells(row):
            return sum([sheet[f"{col}{row}"].value or 0 for col in shipping_date_cols])
        
        # 计算平均值的函数
        def avg_cells(row, days_count):
            total = sum([sheet[f"{col}{row}"].value or 0 for col in shipping_date_cols])
            if days_count == 0:
                return 0
            return round(total / days_count, 1)
        
        # 插入合计和平均数据
        rows_to_calculate = list(range(3, 46))  # 从第3行到第45行
        
        for row in rows_to_calculate:
            # 插入合计数据
            total_value = sum_cells(row)
            insert_excel_by_cell(sheet, f"{sum_col_letter}{row}", total_value, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['title'])
            
            # 插入平均数据
            avg_value = avg_cells(row, days_count)
            insert_excel_by_cell(sheet, f"{avg_col_letter}{row}", avg_value, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['title'])
                
        # 保存Excel文件
        filename = '报表数据.xlsx'
        return save_excel(wb, filename)

    def _get_unprocessed_orders_for_date(self, target_date):
        """获取指定日期的未处理订单数据"""
        try:
            # 将日期转换为日期范围 (当天的开始到结束)
            start_datetime = timezone.make_aware(
                datetime.combine(target_date, datetime.min.time())
            )
            end_datetime = timezone.make_aware(
                datetime.combine(target_date, datetime.max.time())
            )
            
            # 查询指定日期的未处理订单记录
            unprocessed_record = UnprocessedOrder.objects.filter(
                create_time__range=[start_datetime, end_datetime],
                is_delete=False
            ).first()
            
            if unprocessed_record and unprocessed_record.json_data:
                return unprocessed_record.json_data
            else:
                # 如果没有数据，返回空的结构
                return {
                    'sentBefore16': {
                        'localOrder': '',
                        'transferOrder': '',
                        'changeCodeOrder': '',
                        'aftersaleOrder': '',
                        'adjustOrder': ''
                    },
                    'unsentBetween16And17': {
                        'localOrder': '',
                        'transferOrder': '',
                        'changeCodeOrder': '',
                        'aftersaleOrder': '',
                        'adjustOrder': ''
                    },
                    'unsentBetween17And18': {
                        'localOrder': '',
                        'transferOrder': '',
                        'changeCodeOrder': '',
                        'aftersaleOrder': '',
                        'adjustOrder': ''
                    }
                }
        except Exception as e:
            logger.error(f"获取日期 {target_date} 的未处理订单数据失败: {str(e)}")
            # 返回空的结构
            return {
                'sentBefore16': {
                    'localOrder': '',
                    'transferOrder': '',
                    'changeCodeOrder': '',
                    'aftersaleOrder': '',
                    'adjustOrder': ''
                },
                'unsentBetween16And17': {
                    'localOrder': '',
                    'transferOrder': '',
                    'changeCodeOrder': '',
                    'aftersaleOrder': '',
                    'adjustOrder': ''
                },
                'unsentBetween17And18': {
                    'localOrder': '',
                    'transferOrder': '',
                    'changeCodeOrder': '',
                    'aftersaleOrder': '',
                    'adjustOrder': ''
                }
            }
    
    def _get_order_qa_for_date(self, date):
        """获取指定日期的订单问答数据"""
        from .models import OrderQA
        
        order_qas = OrderQA.objects.filter(
            create_time__gte=date,
            create_time__lt=date + timedelta(days=1)
        )
        
        # 按时间段统计
        time_slot_stats = {}
        for choice in OrderQA.TIME_SLOT_CHOICES:
            time_slot_qas = order_qas.filter(time_slot=choice[0])
            time_slot_stats[choice[0]] = {
                'display': choice[1],
                'count': time_slot_qas.count(),
                'users': list(time_slot_qas.values_list('created_by__username', flat=True).distinct())
            }
        
        return time_slot_stats
    
    def _insert_shipping_data_to_excel(self, sheet, col_letter, local_orders, transfer_orders, 
                                     aftersale_orders, relocation_orders, cross_orders, 
                                     return_orders, inventory_receive_orders, repair_orders,
                                     font_size=12, height=20):
        """插入收发货统计数据到Excel"""
        # 计算各种订单数据
        local_regular_count = 0
        local_regular_box_count = 0
        local_changecode_count = 0
        local_changecode_box_count = 0
        local_changecode_id_count = 0
        local_pickup_count = 0
        local_pickup_box_count = 0
        
        for order in local_orders:
            if order.is_changecode:
                local_changecode_count += 1
                local_changecode_box_count += safe_int(order.box_count)
                local_changecode_id_count += safe_int(order.changecode_id_count)
                # 如果是自提+改码，也要记录PickUp信息
                if order.is_pickup:
                    local_pickup_count += 1
                    local_pickup_box_count += safe_int(order.box_count)
            elif order.is_pickup:
                # 自提单计入本地非改码单的统计（创建日期）
                local_regular_count += 1
                local_regular_box_count += safe_int(order.box_count)
                # 同时记录自提单信息用于PickUp行统计
                local_pickup_count += 1
                local_pickup_box_count += safe_int(order.box_count)
            else:
                local_regular_count += 1
                local_regular_box_count += safe_int(order.box_count)
        
        # 转运单数据
        transfer_regular_count = 0
        transfer_regular_box_count = 0
        transfer_pickup_count = 0
        transfer_pickup_box_count = 0
        
        for order in transfer_orders:
            if order.is_pickup:
                # 自提转运单计入转运单的统计（创建日期）
                transfer_regular_count += 1
                transfer_regular_box_count += safe_int(order.box_count)
                # 同时记录自提转运单信息用于PickUp行统计
                transfer_pickup_count += 1
                transfer_pickup_box_count += safe_int(order.box_count)
            else:
                transfer_regular_count += 1
                transfer_regular_box_count += safe_int(order.box_count)
        
        # 其他订单数据
        aftersale_count = aftersale_orders.count()
        aftersale_box_count = sum(safe_int(order.box_count) for order in aftersale_orders)
        aftersale_id_count = sum(safe_int(order.id_count) for order in aftersale_orders)
        
        relocation_count = sum(safe_int(order.order_count) for order in relocation_orders)
        relocation_box_count = sum(safe_int(order.box_count) for order in relocation_orders)
        
        cross_count = sum(safe_int(order.order_count) for order in cross_orders)
        cross_box_count = sum(safe_int(order.box_count) for order in cross_orders)
        
        return_count = sum(safe_int(order.order_count) for order in return_orders)
        return_box_count = sum(safe_int(order.box_count) for order in return_orders)
        
        inventory_count = sum(safe_int(order.order_count) for order in inventory_receive_orders)
        inventory_box_count = sum(safe_int(order.box_count) for order in inventory_receive_orders)
        
        repair_count = sum(safe_int(order.order_count) for order in repair_orders)
        repair_box_count = sum(safe_int(order.box_count) for order in repair_orders)
        
        # 插入数据到Excel - 发货部分 (D3-D15)
        # D3D4: 本地单非改码单（常规单）的单数箱数 - 绿色
        insert_excel_by_cell(sheet, f"{col_letter}3", local_regular_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['local_regular'])
        insert_excel_by_cell(sheet, f"{col_letter}4", local_regular_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['local_regular'])
        
        # D5D6D7: 本地改码单的单数箱数数量 - 黄色
        insert_excel_by_cell(sheet, f"{col_letter}5", local_changecode_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['local_changecode'])
        insert_excel_by_cell(sheet, f"{col_letter}6", local_changecode_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['local_changecode'])
        insert_excel_by_cell(sheet, f"{col_letter}7", local_changecode_id_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['local_changecode'])
        
        # D8D9: 转运单的单数箱数 - 橙色
        insert_excel_by_cell(sheet, f"{col_letter}8", transfer_regular_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['transfer'])
        insert_excel_by_cell(sheet, f"{col_letter}9", transfer_regular_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['transfer'])
        
        # D10D11: 跨仓单的单数箱数 - 紫色
        insert_excel_by_cell(sheet, f"{col_letter}10", cross_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['cross_warehouse'])
        insert_excel_by_cell(sheet, f"{col_letter}11", cross_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['cross_warehouse'])
        
        # D12D13: 包裹回国的单数箱数 - 灰色
        insert_excel_by_cell(sheet, f"{col_letter}12", return_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['return_country'])
        insert_excel_by_cell(sheet, f"{col_letter}13", return_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['return_country'])
        
        # D14D15: 发货合计的单数箱数 - 深绿色
        shipment_order_total = local_regular_count + local_changecode_count + transfer_regular_count + cross_count + return_count
        shipment_box_total = local_regular_box_count + local_changecode_box_count + transfer_regular_box_count + cross_box_count + return_box_count
        insert_excel_by_cell(sheet, f"{col_letter}14", shipment_order_total, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['shipping_total'])
        insert_excel_by_cell(sheet, f"{col_letter}15", shipment_box_total, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['shipping_total'])
        
        # 收货部分 (D16-D26)
        # D16D17: 调仓单的单数箱数 - 绿色
        insert_excel_by_cell(sheet, f"{col_letter}16", relocation_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['relocation'])
        insert_excel_by_cell(sheet, f"{col_letter}17", relocation_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['relocation'])
        
        # D18D19D20: 退换货的单数箱数数量 - 黄色
        insert_excel_by_cell(sheet, f"{col_letter}18", aftersale_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['aftersale'])
        insert_excel_by_cell(sheet, f"{col_letter}19", aftersale_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['aftersale'])
        insert_excel_by_cell(sheet, f"{col_letter}20", aftersale_id_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['aftersale'])
        
        # D21D22: 库存收货的单数箱数 - 橙色
        insert_excel_by_cell(sheet, f"{col_letter}21", inventory_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['inventory'])
        insert_excel_by_cell(sheet, f"{col_letter}22", inventory_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['inventory'])
        
        # D23D24: 回国维修收货的单数箱数 - 紫色
        insert_excel_by_cell(sheet, f"{col_letter}23", repair_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['repair'])
        insert_excel_by_cell(sheet, f"{col_letter}24", repair_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['repair'])
        
        # D25D26: 收货合计的单数箱数 - 深绿色
        receive_order_total = relocation_count + aftersale_count + inventory_count + repair_count
        receive_box_total = relocation_box_count + aftersale_box_count + inventory_box_count + repair_box_count
        insert_excel_by_cell(sheet, f"{col_letter}25", receive_order_total, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['receiving_total'])
        insert_excel_by_cell(sheet, f"{col_letter}26", receive_box_total, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['receiving_total'])
        
        # 收发货合计 (D27-D28) - 深蓝色
        total_order_count = shipment_order_total + receive_order_total
        total_box_count = shipment_box_total + receive_box_total
        insert_excel_by_cell(sheet, f"{col_letter}27", total_order_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['total'])
        insert_excel_by_cell(sheet, f"{col_letter}28", total_box_count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['total'])
        
        # PickUp数据 (D29-D30) - 橙色
        # 注意：PickUp数据应该根据自提日期插入，而不是创建日期
        # 这里先插入0，真正的PickUp数据会在_insert_pickup_data_to_excel中插入
        insert_excel_by_cell(sheet, f"{col_letter}29", 0, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['pickup'])
        insert_excel_by_cell(sheet, f"{col_letter}30", 0, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['pickup'])
    
    def _insert_unprocessed_data_to_excel(self, sheet, col_letter, unprocessed_data, font_size=12, height=20):
        """插入未处理订单数据到Excel"""
        # 16:00之前的数据 (D31-D35)
        sent_before_16 = unprocessed_data.get('sentBefore16', {})
        insert_excel_by_cell(sheet, f"{col_letter}31", sent_before_16.get('localOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_before16'])
        insert_excel_by_cell(sheet, f"{col_letter}32", sent_before_16.get('changeCodeOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_before16'])
        insert_excel_by_cell(sheet, f"{col_letter}33", sent_before_16.get('transferOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_before16'])
        insert_excel_by_cell(sheet, f"{col_letter}34", sent_before_16.get('aftersaleOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_before16'])
        insert_excel_by_cell(sheet, f"{col_letter}35", sent_before_16.get('adjustOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_before16'])
        
        # 16:00-17:00的数据 (D36-D40)
        unsent_16_17 = unprocessed_data.get('unsentBetween16And17', {})
        insert_excel_by_cell(sheet, f"{col_letter}36", unsent_16_17.get('localOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_16_17'])
        insert_excel_by_cell(sheet, f"{col_letter}37", unsent_16_17.get('changeCodeOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_16_17'])
        insert_excel_by_cell(sheet, f"{col_letter}38", unsent_16_17.get('transferOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_16_17'])
        insert_excel_by_cell(sheet, f"{col_letter}39", unsent_16_17.get('aftersaleOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_16_17'])
        insert_excel_by_cell(sheet, f"{col_letter}40", unsent_16_17.get('adjustOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_16_17'])
        
        # 17:00-18:30的数据 (D41-D45)
        unsent_17_18 = unprocessed_data.get('unsentBetween17And18', {})
        insert_excel_by_cell(sheet, f"{col_letter}41", unsent_17_18.get('localOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_17_18'])
        insert_excel_by_cell(sheet, f"{col_letter}42", unsent_17_18.get('changeCodeOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_17_18'])
        insert_excel_by_cell(sheet, f"{col_letter}43", unsent_17_18.get('transferOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_17_18'])
        insert_excel_by_cell(sheet, f"{col_letter}44", unsent_17_18.get('aftersaleOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_17_18'])
        insert_excel_by_cell(sheet, f"{col_letter}45", unsent_17_18.get('adjustOrder', ''), font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['unprocessed_17_18'])
    
    def _insert_order_qa_data_to_excel(self, sheet, col_letter, order_qa_data, font_size=12, height=20):
        """插入订单问答数据到Excel"""
        # 时间段映射（新sheet格式）
        time_slot_mapping = {
            '09:30-10:00': 4,
            '11:30-12:00': 5,
            '13:30-14:00': 6,
            '15:30-16:00': 7,
            '17:30-18:00': 8
        }
        
        total_count = 0
        
        for time_slot, row in time_slot_mapping.items():
            if time_slot in order_qa_data:
                data = order_qa_data[time_slot]
                count = data['count']
                users = data['users']
                
                # 插入用户名到操作人列
                user_text = ','.join(users) if users else ''  # 显示全部用户
                insert_excel_by_cell(sheet, f"{col_letter}{row}", user_text, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['qa_data'])
                
                # 插入数量到数量列
                next_col = get_column_letter(column_index_from_string(col_letter) + 1)
                insert_excel_by_cell(sheet, f"{next_col}{row}", count, font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['qa_data'])
                
                total_count += count
            else:
                # 如果没有数据，插入空值
                insert_excel_by_cell(sheet, f"{col_letter}{row}", '', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['qa_data'])
                next_col = get_column_letter(column_index_from_string(col_letter) + 1)
                insert_excel_by_cell(sheet, f"{next_col}{row}", '', font_size=font_size, is_bold=True, has_border=True, width=15, height=height, fill_color=COLORS['qa_data'])
        
        # 插入总计数到第9行（数量合计）- 合并单元格
        next_col = get_column_letter(column_index_from_string(col_letter) + 1)
        insert_excel_by_cell(sheet, f"{col_letter}9", total_count, font_size=font_size, is_bold=True, has_border=True, merge_range=f'{col_letter}9:{next_col}9', width=15, height=height, fill_color=COLORS['qa_data'])
    
    def _setup_shipping_area(self, sheet, font_size, width, height):
        """设置发货区域"""
        # 发货标题
        insert_excel_by_cell(sheet, 'A3', '发货', merge_range='A3:A15', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['shipping_area'])
        
        # 本地非改码单
        self._setup_order_type(sheet, 'B3', 'B4', '本地非改码单', 'C3', 'C4', ['单数', '箱数'], COLORS['local_regular'], font_size, width, height)
        
        # 本地改码单
        self._setup_order_type(sheet, 'B5', 'B7', '本地改码单', 'C5', 'C7', ['单数', '箱数', '数量'], COLORS['local_changecode'], font_size, width, height)
        
        # 转运单
        self._setup_order_type(sheet, 'B8', 'B9', '转运单', 'C8', 'C9', ['单数', '箱数'], COLORS['transfer'], font_size, width, height)
        
        # 跨仓单
        self._setup_order_type(sheet, 'B10', 'B11', '跨仓单', 'C10', 'C11', ['单数', '箱数'], COLORS['cross_warehouse'], font_size, width, height)
        
        # 包裹回国
        self._setup_order_type(sheet, 'B12', 'B13', '包裹回国', 'C12', 'C13', ['单数', '箱数'], COLORS['return_country'], font_size, width, height)
        
        # 发货合计
        self._setup_order_type(sheet, 'B14', 'B15', '发货合计', 'C14', 'C15', ['单数', '箱数'], COLORS['shipping_total'], font_size, width, height)
    
    def _setup_order_type(self, sheet, start_b, end_b, title, start_c, end_c, labels, color, font_size, width, height):
        """设置订单类型的通用方法"""
        # 设置B列标题
        insert_excel_by_cell(sheet, start_b, title, merge_range=f'{start_b}:{end_b}', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=color)
        
        # 设置C列标签
        for i, label in enumerate(labels):
            row = int(start_c[1:]) + i
            cell = f"C{row}"
            insert_excel_by_cell(sheet, cell, label, font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=color)
    
    def _setup_qa_time_slots(self, qa_sheet, font_size, width, height):
        """设置订单问答时间段"""
        time_slots = [
            ('B4', 'C4', '9:30-10:00'),
            ('B5', 'C5', '11:30-12:00'),
            ('B6', 'C6', '13:30-14:00'),
            ('B7', 'C7', '15:30-16:00'),
            ('B8', 'C8', '17:30-18:00'),
            ('B9', 'C9', '数量合计')
        ]
        
        for start_cell, end_cell, text in time_slots:
            insert_excel_by_cell(qa_sheet, start_cell, text, merge_range=f'{start_cell}:{end_cell}', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['qa_time_slots'])
    
    def _setup_unprocessed_areas(self, sheet, font_size, width, height):
        """设置未处理订单区域 (全部使用灰色)"""
        # 16:00之前
        insert_excel_by_cell(sheet, 'A31', '当天未发出/入库单数16：00之前', merge_range='A31:B35', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['unprocessed_before16'])
        for row in range(31, 36):
            insert_excel_by_cell(sheet, f'C{row}', ['本地单', '改码单', '转运单', '退换货', '调仓单'][row-31], font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['unprocessed_before16'])
        
        # 16:00-17:00
        insert_excel_by_cell(sheet, 'A36', '当天未发出/入库单数16：00~17: 00', merge_range='A36:B40', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['unprocessed_16_17'])
        for row in range(36, 41):
            insert_excel_by_cell(sheet, f'C{row}', ['本地单', '改码单', '转运单', '退换货', '调仓单'][row-36], font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['unprocessed_16_17'])
        
        # 17:00-18:30
        insert_excel_by_cell(sheet, 'A41', '当天未发出/入库单数17：00~18: 30', merge_range='A41:B45', font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['unprocessed_17_18'])
        for row in range(41, 46):
            insert_excel_by_cell(sheet, f'C{row}', ['本地单', '改码单', '转运单', '退换货', '调仓单'][row-41], font_size=font_size, is_bold=True, has_border=True, width=width, height=height, fill_color=COLORS['unprocessed_17_18'])
    
    def _process_order_qa_orders(self, order_qa_orders):
        """处理订单问答数据，按时间段统计"""
        time_slots = {
            '09:30-10:00': {'count': 0, 'users': set()},
            '11:30-12:00': {'count': 0, 'users': set()},
            '13:30-14:00': {'count': 0, 'users': set()},
            '15:30-16:00': {'count': 0, 'users': set()},
            '17:30-18:00': {'count': 0, 'users': set()}
        }
        
        for order in order_qa_orders:
            time_slot = order.time_slot
            if time_slot in time_slots:
                time_slots[time_slot]['count'] += 1
                if order.created_by:
                    time_slots[time_slot]['users'].add(order.created_by.username)
        
        # 转换set为list，便于前端处理
        result = {}
        total_count = 0
        for time_slot, data in time_slots.items():
            result[time_slot] = {
                'count': data['count'],
                'users': list(data['users'])
            }
            total_count += data['count']
        
        result['total_count'] = total_count
        return result
    
    def _insert_order_qa_statistics_to_excel(self, qa_sheet, start_date, end_date, font_size, height):
        """插入订单问答统计信息到Excel A11行开始"""
        try:
            # 获取订单问答统计数据
            order_qa_orders = OrderQA.objects.filter(create_time__range=(start_date, end_date))
            
            # 按时间段统计
            time_slot_stats = {}
            for choice in OrderQA.TIME_SLOT_CHOICES:
                count = order_qa_orders.filter(time_slot=choice[0]).count()
                time_slot_stats[choice[0]] = {
                    'display': choice[1],
                    'count': count
                }
            
            # 按用户统计
            user_stats = order_qa_orders.values('created_by__username').annotate(
                count=Count('id')
            ).order_by('-count')
            
            # 总体统计
            total_records = order_qa_orders.count()
            
            # 插入统计标题
            insert_excel_by_cell(qa_sheet, 'A11', '统计信息', merge_range='A11:C11', font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_title'])
            
            # 插入时间段统计
            row = 12
            insert_excel_by_cell(qa_sheet, f'A{row}', '时间段统计', merge_range=f'A{row}:B{row}', font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_time_slots'])
            row += 1
            
            for time_slot, data in time_slot_stats.items():
                insert_excel_by_cell(qa_sheet, f'A{row}', data['display'], font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_data'])
                insert_excel_by_cell(qa_sheet, f'B{row}', data['count'], font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_data'])
                row += 1
            
            # 插入总计
            insert_excel_by_cell(qa_sheet, f'A{row}', '总计', font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_data'])
            insert_excel_by_cell(qa_sheet, f'B{row}', total_records, font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_data'])
            row += 2
            
            # 插入用户统计
            insert_excel_by_cell(qa_sheet, f'A{row}', '用户统计', merge_range=f'A{row}:B{row}', font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_time_slots'])
            row += 1
            
            for user_stat in user_stats[:10]:  # 只显示前10个用户
                username = user_stat['created_by__username'] or '未知用户'
                count = user_stat['count']
                insert_excel_by_cell(qa_sheet, f'A{row}', username, font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_data'])
                insert_excel_by_cell(qa_sheet, f'B{row}', count, font_size=font_size, is_bold=True, has_border=True, width=20, height=height, fill_color=COLORS['qa_data'])
                row += 1
                
        except Exception as e:
            logger.error(f"插入订单问答统计信息失败: {str(e)}")
            # 如果统计信息插入失败，不影响整个导出流程

    def _collect_pickup_data_for_date(self, local_orders, transfer_orders):
        """收集指定日期的自提单数据"""
        pickup_data = {}
        
        # 收集本地自提单数据
        for order in local_orders:
            if order.is_pickup and order.pickup_time:
                pickup_date = order.pickup_time.date()
                if pickup_date not in pickup_data:
                    pickup_data[pickup_date] = {'local_count': 0, 'local_box_count': 0, 'transfer_count': 0, 'transfer_box_count': 0}
                pickup_data[pickup_date]['local_count'] += 1
                pickup_data[pickup_date]['local_box_count'] += safe_int(order.box_count)
                # 调试信息
                # logger.info(f"收集自提单: ID={order.id}, is_changecode={order.is_changecode}, is_pickup={order.is_pickup}, pickup_time={order.pickup_time}, pickup_date={pickup_date}, box_count={order.box_count}")
        
        # 收集转运自提单数据
        for order in transfer_orders:
            if order.is_pickup and order.pickup_time:
                pickup_date = order.pickup_time.date()
                if pickup_date not in pickup_data:
                    pickup_data[pickup_date] = {'local_count': 0, 'local_box_count': 0, 'transfer_count': 0, 'transfer_box_count': 0}
                pickup_data[pickup_date]['transfer_count'] += 1
                pickup_data[pickup_date]['transfer_box_count'] += safe_int(order.box_count)
                # 调试信息
                # logger.info(f"收集转运自提单: ID={order.id}, is_pickup={order.is_pickup}, pickup_date={pickup_date}, box_count={order.box_count}")
        
        # logger.info(f"收集到的自提单数据: {pickup_data}")
        return pickup_data

    def _collect_all_pickup_data(self, start_date, end_date):
        """统一收集所有自提单数据，按自提日期分组"""
        pickup_data_by_date = {}
        
        # 收集所有在报表日期范围内创建的自提单
        local_orders = LocalOrder.objects.filter(
            create_time__gte=start_date, 
            create_time__lt=end_date,
            is_pickup=True,
            pickup_time__isnull=False
        )
        
        transfer_orders = TransferOrder.objects.filter(
            create_time__gte=start_date, 
            create_time__lt=end_date,
            is_pickup=True,
            pickup_time__isnull=False
        )
        
        print(f"找到本地自提单数量: {local_orders.count()}")
        print(f"找到转运自提单数量: {transfer_orders.count()}")
        
        # 收集本地自提单数据
        for order in local_orders:
            if order.pickup_time:
                # 处理时区问题，转换为本地日期
                # 将UTC时间转换为日本时间（UTC+9）
                import pytz
                jst = pytz.timezone('Asia/Tokyo')
                if order.pickup_time.tzinfo is None:
                    # 如果没有时区信息，假设是UTC
                    pickup_time_utc = pytz.utc.localize(order.pickup_time)
                else:
                    pickup_time_utc = order.pickup_time
                
                pickup_time_jst = pickup_time_utc.astimezone(jst)
                pickup_date = pickup_time_jst.date()
                
                if pickup_date not in pickup_data_by_date:
                    pickup_data_by_date[pickup_date] = {'local_count': 0, 'local_box_count': 0, 'transfer_count': 0, 'transfer_box_count': 0}
                pickup_data_by_date[pickup_date]['local_count'] += 1
                pickup_data_by_date[pickup_date]['local_box_count'] += safe_int(order.box_count)
                print(f"收集本地自提单: ID={order.id}, is_changecode={order.is_changecode}, pickup_time={order.pickup_time}, pickup_time_jst={pickup_time_jst}, pickup_date={pickup_date}, box_count={order.box_count}")
        
        # 收集转运自提单数据
        for order in transfer_orders:
            if order.pickup_time:
                # 处理时区问题，转换为本地日期
                # 将UTC时间转换为日本时间（UTC+9）
                import pytz
                jst = pytz.timezone('Asia/Tokyo')
                if order.pickup_time.tzinfo is None:
                    # 如果没有时区信息，假设是UTC
                    pickup_time_utc = pytz.utc.localize(order.pickup_time)
                else:
                    pickup_time_utc = order.pickup_time
                
                pickup_time_jst = pickup_time_utc.astimezone(jst)
                pickup_date = pickup_time_jst.date()
                
                if pickup_date not in pickup_data_by_date:
                    pickup_data_by_date[pickup_date] = {'local_count': 0, 'local_box_count': 0, 'transfer_count': 0, 'transfer_box_count': 0}
                pickup_data_by_date[pickup_date]['transfer_count'] += 1
                pickup_data_by_date[pickup_date]['transfer_box_count'] += safe_int(order.box_count)
                print(f"收集转运自提单: ID={order.id}, pickup_time={order.pickup_time}, pickup_time_jst={pickup_time_jst}, pickup_date={pickup_date}, box_count={order.box_count}")
        
        print(f"统一收集到的自提单数据: {pickup_data_by_date}")
        return pickup_data_by_date

    def _insert_pickup_data_to_excel(self, sheet, pickup_data_by_date, date_col_letters, start_date, end_date, font_size, height):
        """插入PickUp行数据到Excel"""
        try:
            # 创建日期到列字母的映射 - 只映射报表范围内的日期
            date_to_col = {}
            col_index = 0
            
            # 遍历报表的日期范围，构建日期到列的映射
            for i in range((end_date - start_date).days):
                date = start_date + timedelta(days=i)
                weekday = date.weekday()
                if weekday >= 5:  # 跳过周末
                    continue
                if col_index < len(date_col_letters):
                    # 转换为date类型，确保类型一致
                    date_key = date.date()
                    date_to_col[date_key] = date_col_letters[col_index]
                    # logger.info(f"日期映射: {date_key} -> {date_col_letters[col_index]}")
                    col_index += 1
            
            # logger.info(f"完整的日期映射: {date_to_col}")
            # logger.info(f"收集到的自提单数据: {pickup_data_by_date}")
            
            # 遍历所有收集到的自提单数据
            for pickup_date, counts in pickup_data_by_date.items():
                print(f"处理自提日期: {pickup_date}, 数据: {counts}")
                # 只有当自提日期在报表范围内时才插入数据
                print(f"检查自提日期: {pickup_date} (类型: {type(pickup_date)}), 是否在映射中: {pickup_date in date_to_col}")
                if pickup_date in date_to_col:
                    pickup_col_letter = date_to_col[pickup_date]
                    
                    # 插入PickUp行数据（A29-A30）
                    total_count = counts['local_count'] + counts['transfer_count']
                    total_box_count = counts['local_box_count'] + counts['transfer_box_count']
                    
                    print(f"插入PickUp数据: pickup_date={pickup_date}, col={pickup_col_letter}, count={total_count}, box_count={total_box_count}")
                    
                    insert_excel_by_cell(sheet, f"{pickup_col_letter}29", total_count, 
                                       font_size=font_size, is_bold=True, has_border=True, 
                                       width=15, height=height, fill_color=COLORS['pickup'])
                    insert_excel_by_cell(sheet, f"{pickup_col_letter}30", total_box_count, 
                                       font_size=font_size, is_bold=True, has_border=True, 
                                       width=15, height=height, fill_color=COLORS['pickup'])
                        
        except Exception as e:
            logger.error(f"插入PickUp数据失败: {str(e)}")
            # 如果PickUp数据插入失败，不影响整个导出流程
